Capstone Project - The Battle of the Neighborhoods

Applied Data Science Capstone by IBM/Coursera

Fernando Tauscheck

Table of contents:

  1. Introduction: Business Problem 
     1.1 Curitiba:
  2. Data 
     2.1 Start the code:
 2.2 Foursquare:
     2.2.1 Retrieve Curitiba Coordinates - Google Geocode:
     2.2.2 Calculating reference points to request Venues from FourSquare API:
     2.2.3 Request Venues (and details) to FourSquare API:
         2.2.3.2 Dataframe of Venues:
 2.3 Geographic Data:
     2.3.1 Loading Neighborhoods GeoJSON to Database:
         2.3.1.1 Plotting Neighborhoods:
     2.3.2 Loading Master Plan GeoJSON to Database:
         2.3.2.1 Plotting Master Plan:
     2.3.3 Loading Main Streets GeoJSON to Database:
     2.3.4 Loading Main Streets GeoJSON to Database:
     2.3.5 Socioeconomic data of the neighborhoods:
  3. Methodology:
  4. Analysis: 
     4.1 Creating hexagon to study:
 4.2 Hexagons vs Geographic Data:
     4.2.1 Socioeconomic data of each Hexagons:
     4.2.2 Master Plan of each Hexagons:
     4.2.3 Main Streets of each Hexagons:
     4.2.4 Extras of each Hexagons:
 4.3 Processing data:
     4.3.1 Normalizing:
     4.3.2 Clustering:
 4.4 Bringing Foursquere data into analysis:
     4.4.1 Venues per Hexagon:
     4.4.2 Venues distance to Hexagon:
 4.4 Defining the relevant clusters:
 4.5 Defining the relevant clusters:
 4.6 A peek in the data:
     4.6.1 Hexagon per Population and per Income:
 4.7 Ranking:
  5. Results and Discussion:
  6. Conclusion:

1. Introduction: Business Problem

What defines the success of a commercial business? Can we predict if a point is good enough to open a profitable bakery?

Although the analysis can, in theory, be replicated for any type of business, this report will be targeted to stakeholders interested in opening a bakery in Curitiba, Brazil. We will use geographic and socioeconomic data from existing bakeries to define a short ranked list of possibles location.

1.1 Curitiba:

Curitiba is the capital and largest city in the Brazilian state of Paraná. The city's population was 1,948,626 as of 2020, making it the eighth-most populous city in Brazil and the largest in Brazil's South Region. According to Foursquare, Curitiba has 608 bakeries, of which:

Curitiba

2. Data:

Some factors will influence our analysis:

As a data aggregation tool, RDMBS PostgreSQL will be used with PostGIS.

2.1: Start the code:

2.2 Foursquare:

This project uses the Foursquare API as its main data gathering source as it has a database of millions of venues. To restrict the number of venues to request to Foursquare API, only places classified as bakery were filtered. To mitigate the problem with neighborhoods with more than 100 bakeries (an API limitation), we will query the API in clusters of hexagons with 600m of radius. The coordinates of these hexagons were generated through code, starting from a central point in Curitiba. All points were validated if they were 'within' the Curitiba area through a SQL query. The coordinate of the central point was defined with a request to ‘Google Geocode API’ using the neighborhood ‘Fany’ as the parameter. With the venues list, an additional request was made to retrieve details of each venue:

2.2.1 Retrieve Curitiba Coordinates - Google Geocode:

Starting from a geographically central point in Curitiba (not necessarily in the downtown area), we use the Google Geocode API to obtain the coordinates of this point. These coordinates will be used as the starting point for defining the collection and analysis points, and as the center point of the maps used in this report.

The "Fanny" neighborhood will be the starting point.

2.2.2 Calculating reference points to request Venues from FourSquare API:

With the definition, in the previous function, of the central coordinates, equidistant points (vertices of hexagons) will be defined covering the entire area of the municipality. Starting from these points, the Foursquare API will be questioned (providing a calculated radius).

2.2.3 Request Venues (and details) to FourSquare API:

With the points calculated in the previous function, the Foursquare API is called. As query radius for the API, we used the vertex of the hexagon plus a 1% margin of error.

The output of this code is directly stored in a table on PostgreSQL.

2.2.3.2 Dataframe of Venues:

2.3 Geographic Data:

We will get geographic information from Curitiba at the website of the "Instituto de Pesquisa e Planejamento Urbano de Curitiba" (Institute of Urban Planning and Research of Curitiba also know as IPPUC)1. The Institute provides all sorts of maps of Curitiba. We will use:

These maps are provided in SHP format (ESRI). Posteriorly they were converted to GeoJSON in a proper representation (WGS84). The GeoJSON files was inserted in an RDMBS (PostgreSQL), where will be used the Post GIS extension to analyze. At the GitHub of this project2, you can find the structure of the tables (SQL File).

2.3.1 Loading Neighborhoods GeoJSON to Database:

Load file support/GeoJSON/Curitiba_neighbourhood.geojson into a PostgreSQL table.

  1. https://ippuc.org.br/geodownloads/geo.htm↩

  2. https://github.com/ftauscheck/The-Battle-of-the-Neighborhoods/tree/main/support↩

2.3.1.1 Plotting Neighborhoods:

2.3.2 Loading Master Plan GeoJSON to Database:

Load file support/GeoJSON/Curitiba_master_plan.geojson into a PostgreSQL table.

2.3.2.1 Plotting Master Plan:

2.3.3 Loading Main Streets GeoJSON to Database:

Load file support/GeoJSON/Curitiba_main_streets.geojson into a PostgreSQL table.

2.3.4 Loading Main Streets GeoJSON to Database:

Load files with extra areas into a PostgreSQL table:

2.3.5 Socioeconomic data of the neighborhoods:

The socioeconomic data of the municipality was be collected from the Wikipedia article1: "Lista de bairros de Curitiba".

  1. https://pt.wikipedia.org/wiki/Lista_de_bairros_de_Curitiba↩

3 Methodology:

The objective of this project is to find regions in Curitiba with the best conditions for opening a high-income bakery.

In a first step, we collect all relevant data. Geographical data provided by the city of Curitiba (through IPPUC), socioeconomic data (collected on Wikipedia) and location and classification data of current bakeries were considered. For this, we use the Foursquare API. All data were submitted to tables in PostgreSQL database.

In a second step, there will be data exploration. For this purpose, the city will be divided into hexagons with a radius of 300m. For each of these 'areas', geographic and socioeconomic data will be added that will allow the application of the K algorithm - Nearest Neighbors (KNN).

Only in the third stage of the project the Foursquare data from current venues will be added to the study. With this, we will be able to define the regions with the greatest potential and classify them for our stakeholders.

4 Analysis:

4.1 Creating hexagon to study:

To start, let's create the hexagons that will be the basis of the study.

4.2 Hexagons vs Geographic Data:

Using PostGIS to fill an Pandas dataframe.

4.2.1 Socioeconomic data of each Hexagons:

In the database table, we have a column with the GeoJSON object of each hexagon. Similar information we have in the neighborhood table, with the borders of each neighborhood. Using PostGIS functions and Socioeconomic data extracted from Wikipedia, we will calculate the area of each neighborhood overlapping each hexagon. Applying the proportionality of population and income to the overlapping area (about the neighborhood area), we will have this information for each hexagon.

To reduce the number of hexagons in the study and focus on regions with greater purchasing power, the SQL query filters the hexagons that make up 85% of the municipality's revenue.

4.2.2 Master Plan of each Hexagons:

Applying the same logic as in the previous block, we calculate the overlapping area of each type of zone in the Master Plan.

4.2.3 Main Streets of each Hexagons:

Applying the same logic, we calculate the lenght of each type of Main Streets in each hexagon.

4.2.4 Extras of each Hexagons:

Applying the same logic, we calculate the overlapping area of each type 'extras' in Curitiba.

4.3 Processing data:

As a first action, let's put all data in the same dataframe.

4.3.1 Normalizing:

4.3.2 Clustering:

Using the 'KMeans' function of scikit-learn, we will cluster the study areas into 12 groups.

4.4 Bringing Foursquere data into analysis:

Up to this point in the study, no information from Foursquare has been used, as until then the aim was to classify the points based on their geographic, socioeconomic and legal characteristics. To evolve the analysis, let's add information about the establishments we collected from Foursquere. We will not treat the establishments individually, but based on the hexagons in the study.

4.4.1 Venues per Hexagon:

4.4.2 Venues distance to Hexagon:

4.5 Defining the relevant clusters:

With the information of Bakeries per hexagon and the cluster of each hexagon, we can select the clusters with the highest averages of bakeries per hexagon.

4.6 A peek in the data:

Based on the classification of the clusters, we will analyze the clusters with an average of bakers per hexagon greater than 20% (0.20). In our case it will be clusters 4, 9 and 8.

As we can see in the map above, analyzing only the distance between a hexagon and the closest high-income bakery, the choice would fall to the extreme points (greater distance). But continuing the analysis, we have more information that can help us in the analysis:

4.6.1 Hexagon per Population and per Income:

Analyzing the income and population distribution maps, we have a clear population cleavage, without a direct correlation between population and income (characteristics of the Brazilian population).

According to data from IBGE (Brazilian Institute of Geography and Statistics) in 2019 the GINI index of socioeconomic inequality in Brazil was 0.539, placing the country in 159th position 1.

"In 2019, the 10% share of people with the lowest per capita household income received a share of 0.8% of the total income. Half of the Brazilian population corresponded to 15.6% of the observed income, while the 10% with the highest earnings 42.9% of all income received by people in 2019."

4.7 Ranking:

In an attempt to focus on regions with an adequate balance between population, income and distance to another well-rated bakery (rating greater than 7.5), we propose a metric considering all these variables.

Relevance Equation

To avoid distortions with hexagons with very small distances, we are filtering cases with less than 300m of distance for a bakery.

  1. https://biblioteca.ibge.gov.br/visualizacao/livros/liv101760.pdf↩

Finaly, let's reverse geocode those candidate area centers to get the addresses which can be presented to stakeholders.

With this, we concludes our analysis. We have created a list with 36 zones with similar geographic and socioeconomic characteristics with adequate distance to other well-regarded bakeries in the region. Additionally, we were able to create a metric to assist our stakeholders in defining which regions should be evaluated first.

5 Results and Discussion:

Our analysis shows that although there is a great number of bakerys in Curitiba (~600), there are regions in the city with characteristics that have more bakeries.

The characteristics of this regions can be varied, but analyzing the distribution of points, we can observe that regions with avenues dedicated to the flow of people (either through public transport in segregated lanes, or in lanes with large car capacity) tend to have more bakeries.

After crossing these data, clustering the points and later filtering them, we ended up with a list of 36 addresses that have high potential for a new bakery. This, of course, does not imply that these zones are necessarily the best places for a new bakery. Purpose of this analysis was to only provide info on areas with similarity to other well ranked bakerys and with good distance to avoide unnecessary competition.

6 Conclusion:

Purpose of this project was to identify Curitiba areas in order to aid stakeholders in narrowing down the search for optimal location for a new Bakery. By crossing socioeconomic and geographic information with the distribution os Bakerys from Foursquare data, we create a list of zones of interest and addresses to be used as starting points for final exploration by stakeholders.

Final decision on optimal bakery location will be made by stakeholders based on specific characteristics of neighborhoods and locations in every recommended zone, taking into consideration additional factors like attractiveness of each location (proximity to park or water), levels of noise / proximity to major roads, real estate availability, prices, social and economic dynamics of every neighborhood etc.